const parseQueryParams = require("./parseQueryParams")
const colors = require("colors-console");

/**
 * @author wos
 * @author 2024/01/05
 * 简单的格式化select语句
 * @param query : {a:1,b:2}
 * @param sqlCofig : 配置方法
     * @param sqlCofig.tableName:String 查询的表名 不为空
     * @param sqlCofig.like:Array : 要like的字段, 允许为空
     * @param sqlCofig.timeInterval:Array : 时间区间, 允许为空
     * @param sqlCofig.where:String : 自定义where条件, 允许为空
     * @param sqlCofig.whereJoin:String<'AND'||'OR'> : 自定义where条件连接符, 允许为空
     * @param sqlCofig.orderby:String : 排序字段, 允许为空
 * @ifLimit 是否有limit
 * @returns {string}
 */
function makeSqlSelect(query,  sqlCofig,ifLimit = true){
    if (sqlCofig.tableName === undefined){
        console.log(global.colors(['red'],'[×]' + ' TableName 不能为空'))
        return false
    }
    const tableName = sqlCofig.tableName
    let limit = 'LIMIT ', where = 'WHERE '
    //分页
    const config = global.config
    if(query[config.query.pageNumLable] || query[config.query.pageSizeLable]){
        limit += `${(query[config.query.pageNumLable]-1)*query[config.query.pageSizeLable]}, ${query[config.query.pageSizeLable]}`
        delete query[config.query.pageNumLable]
        delete query[config.query.pageSizeLable]
    }else{
        limit = ''
    }

    //where
    if (sqlCofig.where){
        where = sqlCofig.where
    } else {
        let ifWhere = false //是否存在查询条件
        if (!sqlCofig.like) sqlCofig.like = [] //如果没有like则设置为空
        if (!sqlCofig.timeInterval) sqlCofig.timeInterval = [] //如果没有like则设置为空
        if (!sqlCofig.whereJoin) sqlCofig.whereJoin = 'AND' //如果没有like则设置为空
        for (let i in query){
            if (query[i].length>0){
                let symbolChar = sqlCofig.like.indexOf(i) === -1 ? '=' : 'LIKE'
                if(sqlCofig.timeInterval.indexOf(i) === -1){ //字段查询
                    where += `${i} ${symbolChar} "${symbolChar === '=' ? query[i] : '%'+query[i]+'%'}" ${sqlCofig.whereJoin} `
                }else{ //时间周期查询
                    const timeArr = query[i].split(',')
                    if (timeArr.length === 2){
                        where += `${i} BETWEEN '${timeArr[0]}' AND '${timeArr[1]}' ${sqlCofig.whereJoin} `
                    }
                }
                ifWhere = true
            }
        }
        if (ifWhere){
            where = where.slice(0, -2-sqlCofig.whereJoin.length)
        }else{
            where = ''
        }
    }
    let orderby = sqlCofig.orderby || ''
    let sql = `SELECT * FROM ${tableName} ${where} ${orderby} ${ifLimit ? limit : ''}`
    return sql
}

/**
 *
 * @author wos
 * @author 2024/01/05
 * 简单的格式化update语句
 * @param query : {a:1,b:2}
 * @param sqlCofig : 配置方法
     * @param sqlCofig.tableName:String 查询的表名 不为空
     * @param sqlCofig.noUpdateKey:Array : 不允许更新的字段, 允许为空
     * @param sqlCofig.autoUpdate:Array : 服务器端需要更新的字段, 允许为空
     * @param sqlCofig.where:String : 自定义where条件, 允许为空
 * @returns {string}
 */
function makeSqlUpdate(query, sqlCofig){
    if (sqlCofig.tableName === undefined){
        console.log(global.colors(['red'],'[×]' + ' TableName 不能为空'))
        return false
    }
    const tableName = sqlCofig.tableName
    //格式化条件
    let where = 'WHERE '
    for(let i in sqlCofig.where){
        if (query[sqlCofig.where[i]] === undefined){
            return false
        }else{
            where += `${sqlCofig.where[i]} = "${query[sqlCofig.where[i]]}" AND `
        }
    }
    where = where.slice(0, -4)

    if (!sqlCofig.noUpdateKey) sqlCofig.noUpdateKey = []
    let update = ''
    //插入服务器部署的数据
    for(let i in sqlCofig.autoUpdate){
        sqlCofig.noUpdateKey.push(i)
        update += `${i} = "${sqlCofig.autoUpdate[i]}",`
    }
    //更新用户插入数据
    for(let i in query){
        //判断是否需要更新 noUpdateKey
        if (sqlCofig.noUpdateKey.indexOf(i) === -1){
            if (
                query[i] !== undefined ||
                query[i] !== '' ||
                query[i] != 'null' ||
                query[i] !== null
            ){
                update += `${i} = "${query[i]}",`
            }
        }
    }
    update = update.slice(0, -1)

    return `UPDATE ${tableName} SET ${update} ${where}`
}

/**
 *
 * @author wos
 * @author 2024/01/05
 * 简单的格式化insert into语句
 * @param query : {a:1,b:2}
 * @param sqlCofig : 配置方法
 * @param sqlCofig.tableName:String 查询的表名 不为空
 * @param sqlCofig.autoUpdate:Array : 服务器端需要更新的字段, 允许为空
 * @returns {string}
 */
function makeSqlInsert(query, sqlCofig){
    if (sqlCofig.tableName === undefined){
        console.log(global.colors(['red'],'[×]' + ' TableName 不能为空'))
        return false
    }
    const tableName = sqlCofig.tableName
    let keys = ''
    let values = ''
    if (!sqlCofig.noUpdateKey) sqlCofig.noUpdateKey = []
    for(let i in sqlCofig.autoUpdate){
        sqlCofig.noUpdateKey.push(i)
        keys += `${i},`
        values += `"${sqlCofig.autoUpdate[i]}",`
    }
    for(let i in query){
        if (sqlCofig.noUpdateKey.indexOf(i) === -1){
            if (
                query[i] !== undefined ||
                query[i] !== '' ||
                query[i] != 'null' ||
                query[i] !== null
            ){
                keys += `${i},`
                values += `"${query[i]}",`
            }
        }
    }

    keys = keys.slice(0, -1)
    values = values.slice(0, -1)
    return `INSERT INTO ${tableName} (${keys}) VALUES (${values})`
}
module.exports = {
    makeSqlSelect,
    makeSqlUpdate,
    makeSqlInsert
}